﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Entity;
using System.Data.SqlClient;
using System.Data;
using System.Data.Sql;
using System.Data.SqlTypes;
using System.Web.Configuration;

namespace DAL
{
    public class EnderecoWrapper : BaseWrapper, IWrapper<Endereco>
    {
        public List<Endereco> GetAll()
        {
            List<Endereco> LISTA = new List<Endereco>();

            SqlConnection cn = new SqlConnection(WebConfigurationManager.ConnectionStrings["geoschool"].ConnectionString);
            SqlCommand cmd = new SqlCommand("SP_ENDERECO_GETALL", cn);
            cmd.CommandType = CommandType.StoredProcedure;

            ////Parametros
            //// cmd.Parameters.AddWithValue("@NomeCampo", value)

            try
            {
                cn.Open();
                SqlDataReader dr = cmd.ExecuteReader();

                while (dr.Read())
                {

                    Endereco end = new Endereco();
                    end.IDENDERECO = int.Parse(dr["IDENDERECO"].ToString());
                    end.CEP = dr["CEP"].ToString();
                    end.IDCIDADE = int.Parse(dr["IDCIDADE"].ToString());
                    end.LOGRADOURO =   (dr["LOGRADOURO"].ToString());
                    end.BAIRRO =       (dr["BAIRRO"].ToString());
                    end.NUMERO =       (dr["NUMERO"].ToString());
                    end.COMPLEMENTO =  (dr["COMPLEMENTO"].ToString());
                    end.LATITUDE =     (dr["LATITUDE"].ToString());
                    end.LONGITUDE =    (dr["LONGITUDE"].ToString());

                    LISTA.Add(end);

                }
            }
            catch (SqlException ex)
            {
                Messages += "Erro na inserção de dados\n";
                Messages += ex.Message.ToString();
                Logs.MENSAGEM = Messages;
                Logs.TIPOLOG = "Leitura de dados";
                LOGsWrapper LogsWrapper = new LOGsWrapper();
                LogsWrapper.Insert(Logs);
            }
            finally
            {
                ;
                cn.Close();
            }
            return LISTA;

        }

        public Endereco GetById(int id)
        {
            Endereco end = new Endereco();
            SqlConnection cn = new SqlConnection(ConnectionString());
            SqlCommand cmd = new SqlCommand("SP_ENDERECO_GETBYID", cn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@IDENDERECO", id);


            try
            {
                cn.Open();
                SqlDataReader dr = cmd.ExecuteReader();

                while (dr.Read())
                {

                    end.IDENDERECO = int.Parse(dr["IDENDERECO"].ToString());
                    end.CEP = dr["CEP"].ToString();
                    end.IDCIDADE = int.Parse(dr["IDCIDADE"].ToString());
                    end.LOGRADOURO = (dr["LOGRADOURO"].ToString());
                    end.BAIRRO = (dr["BAIRRO"].ToString());
                    end.NUMERO = (dr["NUMERO"].ToString());
                    end.COMPLEMENTO = (dr["COMPLEMENTO"].ToString());
                    end.LATITUDE = (dr["LATITUDE"].ToString());
                    end.LONGITUDE = (dr["LONGITUDE"].ToString());

                }
            }
            catch (SqlException ex)
            {
                Messages += "Erro na inserção de dados\n";
                Messages += ex.Message.ToString();
                Logs.MENSAGEM = Messages;
                Logs.TIPOLOG = "Leitura de dados";
                LOGsWrapper LogsWrapper = new LOGsWrapper();
                LogsWrapper.Insert(Logs);
            }
            finally
            {
                ;
                cn.Close();
            }
            return end;
        }

        public bool Insert(Endereco entity)
        {
            bool status = false;
            SqlConnection cn = new SqlConnection(ConnectionString());
            SqlCommand cmd = new SqlCommand("SP_ENDERECO_INSERT", cn);
            cmd.CommandType = CommandType.StoredProcedure;

            SqlParameter parametro = new SqlParameter("@IDENDERECO", DbType.UInt32);
            parametro.Direction = ParameterDirection.Output;

            cmd.Parameters.Add(parametro);

            
            cmd.Parameters.AddWithValue("@CEP", entity.CEP);
            cmd.Parameters.AddWithValue("@IDCIDADE", entity.IDCIDADE);
            cmd.Parameters.AddWithValue("@LOGRADOURO", entity.LOGRADOURO);
            cmd.Parameters.AddWithValue("@BAIRRO", entity.BAIRRO);
            cmd.Parameters.AddWithValue("@NUMERO", entity.NUMERO);
            cmd.Parameters.AddWithValue("@COMPLEMENTO", entity.COMPLEMENTO);
            cmd.Parameters.AddWithValue("@LATITUDE", entity.LATITUDE);
            cmd.Parameters.AddWithValue("@LONGITUDE", entity.LONGITUDE);

            ////Parametros
            //// cmd.Parameters.AddWithValue("@NomeCampo", value)

            try
            {
                cn.Open();
                if (cmd.ExecuteNonQuery() > 0)
                    status = true;
                entity.IDENDERECO = int.Parse(cmd.Parameters[0].Value.ToString());

            }
            catch (SqlException ex)
            {
                Messages += "Erro na inserção de dados\n";
                Messages += ex.Message.ToString();

            }
            finally
            {
                if (cn.State != ConnectionState.Closed)
                    cn.Close();
            }

            return status;
        }

        public bool Update(Endereco entity)
        {
            bool status = false;
            SqlConnection cn = new SqlConnection(ConnectionString());
            SqlCommand cmd = new SqlCommand("SP_ENDERECO_UPDATE", cn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@IDENDERECO", entity.IDENDERECO);
            cmd.Parameters.AddWithValue("@CEP", entity.CEP);
            cmd.Parameters.AddWithValue("@IDCIDADE", entity.IDCIDADE);
            cmd.Parameters.AddWithValue("@LOGRADOURO", entity.LOGRADOURO);
            cmd.Parameters.AddWithValue("@BAIRRO", entity.BAIRRO);
            cmd.Parameters.AddWithValue("@NUMERO", entity.NUMERO);
            cmd.Parameters.AddWithValue("@COMPLEMENTO", entity.COMPLEMENTO);
            cmd.Parameters.AddWithValue("@LATITUDE", entity.LATITUDE);
            cmd.Parameters.AddWithValue("@LONGITUDE", entity.LONGITUDE);


            try
            {
                cn.Open();
                if (cmd.ExecuteNonQuery() > 0)
                    status = true;

            }
            catch (SqlException ex)
            {
                Messages += "Erro na inserção de dados\n";
                Messages += ex.Message.ToString();
                Logs.MENSAGEM = Messages;
                Logs.TIPOLOG = "Manipulação de dados";
                LOGsWrapper LogsWrapper = new LOGsWrapper();
                LogsWrapper.Insert(Logs);
            }
            finally
            {
                if (cn.State != ConnectionState.Closed)
                    cn.Close();
            }

            return status;
        }

        public bool Delete(Endereco entity)
        {
            bool status = false;
            SqlConnection cn = new SqlConnection(ConnectionString());
            SqlCommand cmd = new SqlCommand("SP_ENDERECO_DELETE", cn);
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@IDENDERECO", entity.IDENDERECO);

            try
            {
                cn.Open();
                cmd.ExecuteNonQuery();
                status = true;
            }
            catch (SqlException ex)
            {
                Messages += "Erro na exclusão dos dados\n";
                Messages += ex.Message.ToString();
                Logs.MENSAGEM = Messages;
                Logs.TIPOLOG = "Manipulação de dados";
                LOGsWrapper LogsWrapper = new LOGsWrapper();
                LogsWrapper.Insert(Logs);
            }
            finally
            {
                if (cn.State != ConnectionState.Closed)
                    cn.Close();
            }

            return status;
        }

        public Endereco GetByIdEstudante(int id)
        {
            Endereco end = new Endereco();
            SqlConnection cn = new SqlConnection(ConnectionString());
            SqlCommand cmd = new SqlCommand("SP_ENDERECO_GETBYIDESTUDANTE", cn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@IDESTUDANTE", id);


            try
            {
                cn.Open();
                SqlDataReader dr = cmd.ExecuteReader();

                while (dr.Read())
                {

                    end.IDENDERECO = int.Parse(dr["IDENDERECO"].ToString());
                    end.CEP = dr["CEP"].ToString();
                    end.IDCIDADE = int.Parse(dr["IDCIDADE"].ToString());
                    end.LOGRADOURO = (dr["LOGRADOURO"].ToString());
                    end.BAIRRO = (dr["BAIRRO"].ToString());
                    end.NUMERO = (dr["NUMERO"].ToString());
                    end.COMPLEMENTO = (dr["COMPLEMENTO"].ToString());
                    end.LATITUDE = (dr["LATITUDE"].ToString());
                    end.LONGITUDE = (dr["LONGITUDE"].ToString());

                }
            }
            catch (SqlException ex)
            {
                Messages += "Erro na inserção de dados\n";
                Messages += ex.Message.ToString();
                Logs.MENSAGEM = Messages;
                Logs.TIPOLOG = "Manipulação de dados";
                LOGsWrapper LogsWrapper = new LOGsWrapper();
                LogsWrapper.Insert(Logs);
            }
            finally
            {
                ;
                cn.Close();
            }
            return end;
        }

        public int GetEstadoBySigla(string sigla)
        {
            int id = 0;
             SqlConnection cn = new SqlConnection(ConnectionString());
             SqlCommand cmd = new SqlCommand("SP_ESTADO_GETBYSIGLA", cn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@SIGLA", sigla);

   
            try
            {
                cn.Open();
                SqlDataReader dr = cmd.ExecuteReader();

                if (dr.Read())
                {
                    id = int.Parse(dr["IDESTADO"].ToString());
                }
            }
            catch
            {
                Messages += "Estado não encontrado";
            }
            finally
            {
                ;
                cn.Close();
            }

            return id;
        }


        public Endereco GetByIdEscola(int id)
        {
            Endereco end = new Endereco();
            SqlConnection cn = new SqlConnection(ConnectionString());
            SqlCommand cmd = new SqlCommand("SP_ENDERECO_GETBYIDESCOLA", cn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@IDESCOLA", id);


            try
            {
                cn.Open();
                SqlDataReader dr = cmd.ExecuteReader();

                while (dr.Read())
                {

                    end.IDENDERECO = int.Parse(dr["IDENDERECO"].ToString());
                    end.CEP = dr["CEP"].ToString();
                    end.IDCIDADE = int.Parse(dr["IDCIDADE"].ToString());
                    end.LOGRADOURO = (dr["LOGRADOURO"].ToString());
                    end.BAIRRO = (dr["BAIRRO"].ToString());
                    end.NUMERO = (dr["NUMERO"].ToString());
                    end.COMPLEMENTO = (dr["COMPLEMENTO"].ToString());
                    end.LATITUDE = (dr["LATITUDE"].ToString());
                    end.LONGITUDE = (dr["LONGITUDE"].ToString());

                }
            }
            catch (SqlException ex)
            {
                Messages += "Erro na inserção de dados\n";
                Messages += ex.Message.ToString();
            }
            finally
            {
                ;
                cn.Close();
            }
            return end;
        }
    }
}
